package com.quanz.ckgl.mvc;

import static org.crank.crud.criteria.Comparison.eq;
import static org.crank.crud.criteria.Comparison.like;

import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.crank.crud.criteria.Criterion;
import org.crank.crud.criteria.Group;
import org.crank.crud.criteria.OrderBy;
import org.joda.time.DateTime;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.ModelAttribute;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.quanz.ckgl.services.DeliveryOrder;
import com.quanz.ckgl.services.Product;
import com.quanz.ckgl.services.ProductService;
import com.quanz.ckgl.services.Rack;
import com.quanz.ckgl.services.RackService;
import com.quanz.ckgl.services.Stock;
import com.quanz.ckgl.services.StockService;
import com.quanz.ckgl.services.User;
import com.quanz.ckgl.services.UserService;
import com.quanz.ckgl.services.WarehouseWarrant;
import com.quanz.ckgl.tools.AuthHelper;
import com.quanz.ckgl.tools.Expression;
import com.quanz.ckgl.tools.ExpressionUtils;

/**
 * 库存管理controller
 * 
 * @author James Quan
 * @version 2015年10月15日 下午9:26:46
 */
@RestController
public class StockController {

	private StockService stockService;
	private ProductService productService;
	private RackService rackService;
	private UserService userService;

	// 界面字段与实际字段的映射表
	private Map<String, String> stockFieldAlias;
	private Map<String, String> wvFieldAlias;
	private Map<String, String> dvFieldAlias;

	public StockController() {
		super();
		stockFieldAlias = new HashMap<String, String>();
		stockFieldAlias.put("productId", "product.id");
		stockFieldAlias.put("productName", "product.name");
		stockFieldAlias.put("vendor", "product.vendor");
		stockFieldAlias.put("phone", "product.phone");
		stockFieldAlias.put("rackId", "rack.id");
		stockFieldAlias.put("rackName", "rack.name");

		wvFieldAlias = new HashMap<String, String>();
		wvFieldAlias.put("productId", "product.id");
		wvFieldAlias.put("productName", "product.name");
		wvFieldAlias.put("vendor", "product.vendor");
		wvFieldAlias.put("phone", "product.phone");
		wvFieldAlias.put("rackId", "rack.id");
		wvFieldAlias.put("rackName", "rack.name");
		wvFieldAlias.put("userId", "user.id");
		wvFieldAlias.put("userName", "user.name");

		dvFieldAlias = new HashMap<String, String>();
		dvFieldAlias.put("productId", "product.id");
		dvFieldAlias.put("productName", "product.name");
		dvFieldAlias.put("vendor", "product.vendor");
		dvFieldAlias.put("phone", "product.phone");
		dvFieldAlias.put("rackId", "rack.id");
		dvFieldAlias.put("rackName", "rack.name");
		dvFieldAlias.put("userId", "user.id");
		wvFieldAlias.put("userName", "user.name");
	}

	@Autowired
	public void setStockService(StockService stockService) {
		this.stockService = stockService;
	}

	@Autowired
	public void setProductService(ProductService productService) {
		this.productService = productService;
	}

	@Autowired
	public void setRackService(RackService rackService) {
		this.rackService = rackService;
	}

	@Autowired
	public void setUserService(UserService userService) {
		this.userService = userService;
	}

	@RequestMapping(value = "/stocks_processing.json", method = { RequestMethod.GET, RequestMethod.POST })
	public DataTablesResponse stocks_processing(@ModelAttribute DataTablesRequest request) {
		DataTablesSearch search = request.getSearch();
		Criterion criterion = null;
		if (search != null && !StringUtils.isEmpty(search.getValue())) {
			String v = "%" + search.getValue() + "%";
			criterion = Group.or(like("product.id", v), like("product.name", v), like("product.vendor", v),
					like("product.phone", v), like("rack.id", v), like("rack.name", v));
		}
		int count = stockService.findStockCount(criterion);
		DataTablesResponse resp = new DataTablesResponse();
		resp.setDraw(request.getDraw());
		resp.setRecordsFiltered(count);
		resp.setTotalRecords(count);
		if (count > 0) {
			DataTablesOrder[] orders = request.getOrder();
			OrderBy[] orderBy = null;
			if (orders != null && orders.length > 0) {
				DataTablesColumn[] columns = request.getColumns();
				orderBy = new OrderBy[orders.length];
				for (int i = 0; i < orders.length; i++) {
					DataTablesOrder order = orders[i];
					String column = columns[order.getColumn()].getData();
					String field = stockFieldAlias.containsKey(column) ? stockFieldAlias.get(column) : column;
					orderBy[i] = "asc".equals(order.getDir()) ? OrderBy.asc(field) : OrderBy.desc(field);
				}
			}
			Stock[] products = stockService.findStock(orderBy, request.getStart(), request.getLength(), criterion);
			resp.setData(products);
		} else {
			resp.setData(new Stock[0]);
		}
		return resp;
	}

	@RequestMapping("/stock/{id}")
	public Stock getStockById(@PathVariable long id) {
		Stock stock = stockService.findStockById(id);
		return stock;
	}

	@RequestMapping(value = "/stock", method = RequestMethod.POST)
	public Stock createStock(Stock stock) {
		String productId = stock.getProductId();
		String rackId = stock.getRackId();
		Product product = productService.findProductById(productId);
		if (product == null) {
			throw new IllegalArgumentException("product " + productId + " not found!");
		}
		Rack rack = rackService.findRackById(rackId);
		if (rack == null) {
			throw new IllegalArgumentException("rack " + rackId + " not found!");
		}
		int layer = stock.getLayer();
		int col = stock.getCol();

		Stock s = new Stock();
		s.setProductId(product.getId());
		s.setProductName(product.getName());
		s.setVendor(product.getVendor());
		s.setPhone(product.getPhone());
		s.setRackId(rack.getId());
		s.setRackNote(rack.getNote());
		s.setLayer(layer);
		s.setCol(col);
		s.setQuantity(stock.getQuantity());
		s.setReceiveTime(stock.getReceiveTime() == null ? DateTime.now() : stock.getReceiveTime());
		s = stockService.createStock(s);
		return s;
	}

	@RequestMapping(value = "/stock/{id}", method = RequestMethod.PUT)
	public boolean updateStock(@PathVariable long id, @ModelAttribute Stock stock) {
		Stock s = stockService.findStockById(id);
		if (s == null) {
			throw new IllegalArgumentException("stock " + id + " not found!");
		}
		String productId = stock.getProductId();
		String productName = stock.getProductName();
		String vendor = stock.getVendor();

		String rackId = stock.getRackId();
		Product product = productService.findProductById(productId);
		if (product == null) {
			throw new IllegalArgumentException("product " + productId + " not found!");
		}
		if (!StringUtils.equals(product.getName(), productName) || !StringUtils.equals(product.getVendor(), vendor)) {
			product.setName(productName);
			product.setVendor(vendor);
			productService.updateProduct(product);
		}
		Rack rack = rackService.findRackById(rackId);
		if (rack == null) {
			throw new IllegalArgumentException("rack " + rackId + " not found!");
		}
		s = new Stock(s);
		s.setProductId(product.getId());
		s.setProductName(product.getName());
		s.setVendor(product.getVendor());
		s.setPhone(product.getPhone());
		s.setRackId(rack.getId());
		s.setRackNote(rack.getNote());
		s.setLayer(stock.getLayer());
		s.setCol(stock.getCol());
		s.setQuantity(stock.getQuantity());
		stockService.updateStock(s);
		return true;
	}

	@RequestMapping(value = "/stock/{id}", method = RequestMethod.DELETE)
	public boolean removeStock(@PathVariable long id) {
		Stock stock = stockService.findStockById(id);
		if (stock != null) {
			stockService.removeStock(stock);
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 列出指定货架上的货物列表
	 * 
	 * @return
	 */
	@RequestMapping(value = "/stock/list/{rackId}", method = RequestMethod.GET)
	public StockInfo[] getStockList(@PathVariable String rackId) {
		Stock[] stocks = stockService.findStock(OrderBy.orderBy(OrderBy.asc("layer"), OrderBy.asc("col")), 0, -1,
				eq("rack.id", rackId));
		StockInfo[] infos = new StockInfo[stocks.length];
		Map<String, Product> products = new HashMap<String, Product>();
		for (int i = 0; i < stocks.length; i++) {
			Stock stock = stocks[i];
			StockInfo info = new StockInfo();
			info.setId(stock.getId());
			info.setRackId(rackId);
			info.setLayer(stock.getLayer());
			info.setCol(stock.getCol());
			info.setQuantity(stock.getQuantity());
			info.setProductId(stock.getProductId());
			info.setProductName(stock.getProductName());
			info.setVendor(stock.getVendor());
			String productId = stock.getProductId();
			Product product = products.get(productId);
			if (product == null) {
				product = productService.findProductById(productId);
				products.put(productId, product);
			}
			if (product != null) {
				info.setProductColor(product.getColor());
			}
			infos[i] = info;
		}
		return infos;
	}

	@RequestMapping(value = "/wv_processing.json", method = { RequestMethod.GET, RequestMethod.POST })
	public DataTablesResponse wv_processing(@ModelAttribute DataTablesRequest request) {
		DataTablesSearch search = request.getSearch();
		Criterion[] criteria = null;
		if (!AuthHelper.isUserHasRole("ADMIN")) {
			// 非管理员只能查看自己的操作记录
			criteria = new Criterion[] { eq("user.account", AuthHelper.getCurrentUser()) };
		}
		if (search != null && !StringUtils.isEmpty(search.getValue())) {
			String v = "%" + search.getValue() + "%";
			Criterion criterion = Group.or(like("product.id", v), like("product.name", v), like("product.vendor", v),
					like("product.phone", v), like("rack.id", v));
			criteria = ArrayUtils.addAll(criteria, criterion);
		}
		Expression[] expressions = request.getSearchExp();
		if (expressions != null && expressions.length > 0) {
			Criterion[] criterions = new Criterion[expressions.length];
			for (int i = 0; i < expressions.length; i++) {
				criterions[i] = ExpressionUtils.getCriteria(expressions[i], wvFieldAlias);
			}
			criteria = ArrayUtils.addAll(criteria, criterions);
		}
		int count = stockService.findWVCount(criteria);
		DataTablesResponse resp = new DataTablesResponse();
		resp.setDraw(request.getDraw());
		resp.setRecordsFiltered(count);
		resp.setTotalRecords(count);
		if (count > 0) {
			DataTablesOrder[] orders = request.getOrder();
			OrderBy[] orderBy = null;
			if (orders != null && orders.length > 0) {
				DataTablesColumn[] columns = request.getColumns();
				orderBy = new OrderBy[orders.length];
				for (int i = 0; i < orders.length; i++) {
					DataTablesOrder order = orders[i];
					String column = columns[order.getColumn()].getData();
					String field = wvFieldAlias.containsKey(column) ? wvFieldAlias.get(column) : column;
					orderBy[i] = "asc".equals(order.getDir()) ? OrderBy.asc(field) : OrderBy.desc(field);
				}
			}
			WarehouseWarrant[] vouchers = stockService.findWV(orderBy, request.getStart(), request.getLength(),
					criteria);
			resp.setData(vouchers);
		} else {
			resp.setData(new WarehouseWarrant[0]);
		}
		return resp;
	}

	@RequestMapping(value = {"/wv/upload","/dv/upload"}, method = RequestMethod.POST)
	public Voucher[] voucherUpload(@RequestParam("file") MultipartFile file)
			throws IOException, EncryptedDocumentException, InvalidFormatException {
		if (file == null) {
			return new Voucher[0];
		}
		InputStream in = file.getInputStream();
		try {
			Workbook workbook = WorkbookFactory.create(in);
			return readwv(workbook);
		} finally {
			in.close();
		}
	}

	@RequestMapping(value = "/wv/batch", method = RequestMethod.POST)
	public WarehouseWarrant[] batchCreateWV(@ModelAttribute UploadVouchers vouchers) {
		DateTime now = DateTime.now();
		List<WarehouseWarrant> list = new ArrayList<WarehouseWarrant>();
		for (Voucher voucher : vouchers.getVouchers()) {
			String productId = voucher.getProductId();
			String rackId = voucher.getRackId();
			Product product = productService.findProductById(productId);
			if (product == null) {
				throw new IllegalArgumentException("product " + productId + " not found!");
			}
			Rack rack = rackService.findRackById(rackId);
			if (rack == null) {
				throw new IllegalArgumentException("rack " + rackId + " not found!");
			}

			WarehouseWarrant v = new WarehouseWarrant();
			v.setProductId(product.getId());
			v.setProductName(product.getName());
			v.setVendor(product.getVendor());
			v.setPhone(product.getPhone());
			v.setRackId(rack.getId());
			v.setRackNote(rack.getNote());
			v.setLayer(voucher.getLayer());
			v.setCol(voucher.getCol());
			v.setQuantity(voucher.getQuantity());
			v.setStorageTime(now);
			User user = getCurrentUser();
			v.setUserId(user.getId());
			v.setUserName(user.getName());
			list.add(v);
		}
		return stockService.stockIn(list.toArray(new WarehouseWarrant[list.size()]));
	}

	@RequestMapping(value = "/wv", method = RequestMethod.POST)
	public WarehouseWarrant createWV(WarehouseWarrant voucher) {
		String productId = voucher.getProductId();
		String rackId = voucher.getRackId();
		Product product = productService.findProductById(productId);
		if (product == null) {
			throw new IllegalArgumentException("product " + productId + " not found!");
		}
		Rack rack = rackService.findRackById(rackId);
		if (rack == null) {
			throw new IllegalArgumentException("rack " + rackId + " not found!");
		}

		WarehouseWarrant v = new WarehouseWarrant();
		v.setProductId(product.getId());
		v.setProductName(product.getName());
		v.setVendor(product.getVendor());
		v.setPhone(product.getPhone());
		v.setRackId(rack.getId());
		v.setRackNote(rack.getNote());
		v.setLayer(voucher.getLayer());
		v.setCol(voucher.getCol());
		v.setQuantity(voucher.getQuantity());
		v.setStorageTime(voucher.getStorageTime() == null ? DateTime.now() : voucher.getStorageTime());
		User user = getCurrentUser();
		v.setUserId(user.getId());
		v.setUserName(user.getName());
		v = stockService.stockIn(v);
		return v;
	}

	@RequestMapping(value = "/dv_processing.json", method = { RequestMethod.GET, RequestMethod.POST })
	public DataTablesResponse dv_processing(@ModelAttribute DataTablesRequest request) {
		DataTablesSearch search = request.getSearch();
		Criterion[] criteria = null;
		if (!AuthHelper.isUserHasRole("ADMIN")) {
			// 非管理员只能查看自己的操作记录
			criteria = new Criterion[] { eq("user.account", AuthHelper.getCurrentUser()) };
		}
		if (search != null && !StringUtils.isEmpty(search.getValue())) {
			String v = "%" + search.getValue() + "%";
			Criterion criterion = Group.or(like("product.id", v), like("product.name", v), like("product.vendor", v),
					like("product.phone", v), like("rack.id", v));
			criteria = ArrayUtils.addAll(criteria, criterion);
		}
		Expression[] expressions = request.getSearchExp();
		if (expressions != null && expressions.length > 0) {
			Criterion[] criterions = new Criterion[expressions.length];
			for (int i = 0; i < expressions.length; i++) {
				criterions[i] = ExpressionUtils.getCriteria(expressions[i], dvFieldAlias);
			}
			criteria = ArrayUtils.addAll(criteria, criterions);
		}
		int count = stockService.findDVCount(criteria);
		DataTablesResponse resp = new DataTablesResponse();
		resp.setDraw(request.getDraw());
		resp.setRecordsFiltered(count);
		resp.setTotalRecords(count);
		if (count > 0) {
			DataTablesOrder[] orders = request.getOrder();
			OrderBy[] orderBy = null;
			if (orders != null && orders.length > 0) {
				DataTablesColumn[] columns = request.getColumns();
				orderBy = new OrderBy[orders.length];
				for (int i = 0; i < orders.length; i++) {
					DataTablesOrder order = orders[i];
					String column = columns[order.getColumn()].getData();
					String field = dvFieldAlias.containsKey(column) ? dvFieldAlias.get(column) : column;
					orderBy[i] = "asc".equals(order.getDir()) ? OrderBy.asc(field) : OrderBy.desc(field);
				}
			}
			DeliveryOrder[] vouchers = stockService.findDV(orderBy, request.getStart(), request.getLength(), criteria);
			resp.setData(vouchers);
		} else {
			resp.setData(new DataTablesOrder[0]);
		}
		return resp;
	}

	@RequestMapping(value = "/dv", method = RequestMethod.POST)
	public DeliveryOrder createDV(DeliveryOrder voucher) {
		String productId = voucher.getProductId();
		String rackId = voucher.getRackId();
		Product product = productService.findProductById(productId);
		if (product == null) {
			throw new IllegalArgumentException("product " + productId + " not found!");
		}
		Rack rack = rackService.findRackById(rackId);
		if (rack == null) {
			throw new IllegalArgumentException("rack " + rackId + " not found!");
		}

		DeliveryOrder v = new DeliveryOrder();
		v.setProductId(product.getId());
		v.setProductName(product.getName());
		v.setVendor(product.getVendor());
		v.setPhone(product.getPhone());
		v.setRackId(rack.getId());
		v.setRackNote(rack.getNote());
		v.setLayer(voucher.getLayer());
		v.setCol(voucher.getCol());
		v.setQuantity(voucher.getQuantity());
		v.setDeliveryTime(voucher.getDeliveryTime() == null ? DateTime.now() : voucher.getDeliveryTime());
		User user = getCurrentUser();
		v.setUserId(user.getId());
		v.setUserName(user.getName());
		v = stockService.stockOut(v);
		return v;
	}

	@RequestMapping(value = "/dv/batch", method = RequestMethod.POST)
	public DeliveryOrder[] batchCreateDV(@ModelAttribute UploadVouchers vouchers) {
		DateTime now = DateTime.now();
		List<DeliveryOrder> list = new ArrayList<DeliveryOrder>();
		for (Voucher voucher : vouchers.getVouchers()) {
			String productId = voucher.getProductId();
			String rackId = voucher.getRackId();
			Product product = productService.findProductById(productId);
			if (product == null) {
				throw new IllegalArgumentException("product " + productId + " not found!");
			}
			Rack rack = rackService.findRackById(rackId);
			if (rack == null) {
				throw new IllegalArgumentException("rack " + rackId + " not found!");
			}

			DeliveryOrder v = new DeliveryOrder();
			v.setProductId(product.getId());
			v.setProductName(product.getName());
			v.setVendor(product.getVendor());
			v.setPhone(product.getPhone());
			v.setRackId(rack.getId());
			v.setRackNote(rack.getNote());
			v.setLayer(voucher.getLayer());
			v.setCol(voucher.getCol());
			v.setQuantity(voucher.getQuantity());
			v.setDeliveryTime(now);
			User user = getCurrentUser();
			v.setUserId(user.getId());
			v.setUserName(user.getName());
			list.add(v);
		}
		return stockService.stockOut(list.toArray(new DeliveryOrder[list.size()]));
	}

	/**
	 * 检查库存是否足够
	 * 
	 * @return
	 */
	@RequestMapping(value = "/dv/check")
	public boolean isQuantityEnough(@RequestParam("rackId") String rackId, @RequestParam("layer") String layer,
			@RequestParam("col") String col, @RequestParam("productId") String productId,
			@RequestParam("quantity") int quantity) {
		Stock[] list = stockService.findStock(null, 0, -1, eq("product.id", productId), eq("rack.id", rackId),
				eq("layer", layer), eq("col", col));
		if (list.length == 0) {
			return false;
		}
		for (Stock stock : list) {
			if (quantity <= 0) {
				break;
			}
			quantity -= stock.getQuantity();
		}
		return quantity <= 0;
	}

	protected User getCurrentUser() {
		return userService.findUserByAccount(AuthHelper.getCurrentUser());
	}

	protected Voucher[] readwv(Workbook workbook) {
		List<Voucher> list = new ArrayList<Voucher>();
		int c = workbook.getNumberOfSheets();
		for (int i = 0; i < c; i++) {
			Sheet sheet = workbook.getSheetAt(i);
			SheetInfo info = getSheetInfo(sheet);
			if (info == null) {
				continue;
			}
			for (int r = info.firstRow + 1; r <= sheet.getLastRowNum(); r++) {
				Row row = sheet.getRow(r);
				if (row == null) {
					continue;
				}
				String id = getCellValue(row.getCell(info.idCol));
				String name = getCellValue(row.getCell(info.nameCol));
				String barcode = getCellValue(row.getCell(info.barcodeCol));
				String unit = getCellValue(row.getCell(info.unitCol));
				String spec = getCellValue(row.getCell(info.specCol));

				Product product = productService.findProductByBarcode(barcode);
				if (product == null) {
					product = productService.findProductById(id);
					if (product == null) {
						product = new Product();
						product.setId(id);
						product.setBarcode(barcode);
						product.setName(name);
						product.setUnit(unit);
						product.setSpec(spec);
						product = productService.createProduct(product);
					}
				}

				Voucher v = new Voucher();
				v.setProductId(product.getId());
				v.setBarcode(product.getBarcode());
				v.setProductName(product.getName());
				// 设置缺省值
				v.setQuantity(1);
				v.setLayer(1);
				v.setCol(1);

				list.add(v);
			}
		}
		return list.toArray(new Voucher[list.size()]);
	}

	protected SheetInfo getSheetInfo(Sheet sheet) {
		for (int r = sheet.getFirstRowNum(); r <= sheet.getLastRowNum(); r++) {
			Row row = sheet.getRow(r);
			if (row == null) {
				continue;
			}
			SheetInfo info = new SheetInfo();
			for (int c = row.getFirstCellNum(); c <= row.getLastCellNum(); c++) {
				Cell cell = row.getCell(c);
				if (cell == null) {
					continue;
				}
				String value = getCellValue(cell);
				if ("商品编号".equals(value)) {
					info.idCol = c;
					info.firstRow = r;
				} else if ("商品名称".equals(value)) {
					info.nameCol = c;
					info.firstRow = r;
				} else if ("商品条码".equals(value)) {
					info.barcodeCol = c;
					info.firstRow = r;
				} else if ("单位".equals(value)) {
					info.unitCol = c;
					info.firstRow = r;
				} else if ("规格型号".equals(value)) {
					info.specCol = c;
					info.firstRow = r;
				}
			}
			if (info.isComplete()) {
				return info;
			}
		}
		return null;
	}

	/**
	 * 获取单元格内文本信息
	 * 
	 * @param cell
	 * @return
	 * @date 2013-5-8
	 */
	protected String getCellValue(Cell cell) {
		if (cell == null) {
			return null;
		}
		String value = "";
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_NUMERIC: // 数字
			if (HSSFDateUtil.isCellDateFormatted(cell)) { // 如果是日期类型
				value = new SimpleDateFormat("yyyyMMddHHmmss").format(cell.getDateCellValue());
			} else {
				value = String.valueOf(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_STRING: // 字符串
			value = cell.getStringCellValue();
			break;
		case Cell.CELL_TYPE_FORMULA: // 公式
			// 用数字方式获取公式结果，根据值判断是否为日期类型
			double numericValue = cell.getNumericCellValue();
			if (HSSFDateUtil.isValidExcelDate(numericValue)) { // 如果是日期类型
				value = new SimpleDateFormat("yyyyMMddHHmmss").format(cell.getDateCellValue());
			} else
				value = String.valueOf(numericValue);
			break;
		case Cell.CELL_TYPE_BLANK: // 空白
			value = StringUtils.EMPTY;
			break;
		case Cell.CELL_TYPE_BOOLEAN: // Boolean
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_ERROR: // Error，返回错误码
			value = String.valueOf(cell.getErrorCellValue());
			break;
		default:
			value = StringUtils.EMPTY;
			break;
		}
		value = StringUtils.trimToEmpty(value);
		if (value.startsWith("'")) {
			value = value.substring(1);
		}
		return value;
	}

	/**
	 * 待导入的excel文件信息
	 */
	protected class SheetInfo {
		public int firstRow = -1;

		public int idCol = -1;
		public int nameCol = -1;
		public int barcodeCol = -1;
		public int unitCol = -1;
		public int specCol = -1;

		public boolean isComplete() {
			return firstRow != -1 && idCol != -1 && nameCol != -1 && barcodeCol != -1 && unitCol != -1 && specCol != -1;
		}
	}

}
